﻿using System;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace tab2sql
{

    /// <summary>
    /// THIS DOESN'T WORK AT ALL.
    /// </summary>
    class TidySQL
    {
        // PUBLIC FIELDS
        // ----------------------------------------------------------------------------
        public string EndResult = "";

        // PRIVATE FIELDS
        // ----------------------------------------------------------------------------
        private string Start = "";
        private string WIP = "";
        private string[] wipArray = new string[10000];


        // CONSTRUCTOR
        // ----------------------------------------------------------------------------
        public TidySQL(string StartString)
        {
            Start = StartString;
            if (Start == "")
            {
                Start = @"select pro_ref, add_1, add_2, add_3, add_4, post_code from property where pro_ref not in ('01000000','01343323') and blk_ref='010' order by pro_ref,add_1";
            }
            Start = encodeString(Start);
            wipArray = DoSplit();

            string[] LineNumbers = new string[3000];
            string[] resultingArray = new string[3000];
            int[] tabCount = new int[3000];
            int[] newLines = new int[3000];
            // 3k is temp arb limit.
            int length = wipArray.Length;
            int currentTab = 0;
            bool[] indentThisLine = new bool[3000];
            bool[] indentNextLine = new bool[3000];
            bool[] unindentNextLine = new bool[3000];
            bool[] lineBreakBefore = new bool[3000];
            bool[] lineBreakAfter = new bool[3000];
            string[] openTag = new string[3000];
            string[] closeTag = new string[3000];


            for (int i = 0; i < length; i++)
            {
                LineNumbers[i] = "Line:" + i.ToString() + "\t";
                wipArray[i] = wipArray[i].Trim().ToLower();

                wipArray[i] = unEncodeString(wipArray[i]);
                indentNextLine[i] = false;
                indentThisLine[i] = false;
                unindentNextLine[i] = false;
                lineBreakAfter[i] = false;
                lineBreakBefore[i] = false;
                tabCount[i] = currentTab;
                newLines[i] = 0;



                if (isKeyword(wipArray[i]) == true)
                {

                    wipArray[i] = wipArray[i].ToUpper();

                }


                //SELECT, FROM, JOIN
                if (isSpecialKeyWord(wipArray[i]) == true)
                {
                    currentTab = 0;
                    lineBreakBefore[i] = true;
                    lineBreakAfter[i] = true;
                    indentNextLine[i] = true;
                    tabCount[i + 1]++;
                }

                // EVERYTHING ELSE

                if (isSpecialKeyWord(wipArray[i]) == false)
                {
                    indentNextLine[i] = false;
                }

                // CONTAINS COMMA

                if (isCommaWithin(wipArray[i]) == true)
                {
                    lineBreakAfter[i] = true;

                }
                // IS THIS A CASE STATEMENT
                if (isCase(wipArray[i]) == true)
                {
                    lineBreakAfter[i] = true;
                    indentNextLine[i] = true;
                }
                // Is this a "IN", "OR" or "AND"
                if (isOnOrAnd(wipArray[i]) == true)
                {
                    currentTab = currentTab--;
                    lineBreakBefore[i] = true;
                    lineBreakAfter[i] = true;
                    indentNextLine[i] = true;
                }
                // Is this a non-breaking key word
                if (isNonBreakingKeyWord(wipArray[i]) == true)
                {
                    lineBreakAfter[i] = false;
                }
                // A When, Then, End or End.
                if (isWhen(wipArray[i]) == true)
                {
                    currentTab = Math.Max(1, currentTab);
                    indentThisLine[i] = true;
                    lineBreakBefore[i] = true;
                    indentNextLine[i] = false;
                    lineBreakAfter[i] = false;
                }

                tabCount[i] = currentTab;
                if (indentNextLine[i] == true)
                {
                    currentTab++;
                    indentNextLine[i] = false;
                }
                // loop ends

            }

            // FORMING STRING

            string endString = "";
            for (int i = 0; i < length; i++)
            {
                if (lineBreakBefore[i] == true)
                {
                    endString += Environment.NewLine;
                }

                endString += DGen.Replicate("\t", tabCount[i]);
                endString += wipArray[i];
                if (lineBreakAfter[i] == true)
                {
                    endString += Environment.NewLine;
                    
                }
                if (lineBreakAfter[i] == false)
                {
                    endString += " ";
                }

            }
            EndResult = eliminateDuplicateNewLines(endString);

        }



        // PRIVATE METHODS
        // ----------------------------------------------------------------------------
        private bool isCommaWithin(string p)
        {
            RegexOptions options = RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase;
            Regex regex = new Regex(@",", options);
            bool isMatch = regex.IsMatch(p);
            return isMatch;
        }

        private bool isNonBreakingKeyWord(string p)
        {
            RegexOptions options = RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase;
            Regex regex = new Regex(@"^null|is|not$", options);
            bool isMatch = regex.IsMatch(p);
            return isMatch;
        }

        private bool isOnOrAnd(string p)
        {
            RegexOptions options = RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase;
            Regex regex = new Regex(@"^on|and|or$", options);
            bool isMatch = regex.IsMatch(p);
            return isMatch;
        }


        private bool isSpecialKeyWord(string p)
        {
            RegexOptions options = RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase;
            Regex regex = new Regex(@"^select|join|where|from|order|group|left join|right join|inner join|outer join|cross join$", options);
            bool isMatch = regex.IsMatch(p);
            //System.Windows.Forms.MessageBox.Show("Test"+p+isMatch.ToString());
            return isMatch;
        }

        private bool isKeyword(string p)
        {
            RegexOptions options = RegexOptions.IgnoreCase;
            Regex regex = new Regex(@"^add$|all|alter|and|any|as|asc|authorization|backup|begin|between|break|browse|bulk|by|cascade|case|check|checkpoint|close|clustered|coalesce|collate|column|commit|compute|constraint|contains|containstable|continue|convert|create|cross|current|current_date|current_time|current_timestamp|current_user|cursor|database|dbcc|deallocate|declare|default|delete|deny|desc|disk|distinct|distributed|double|drop|dummy|dump|else|end|errlvl|escape|except|exec|execute|exists|exit|fetch|file|fillfactor|for|foreign|freetext|freetexttable|from|full|function|goto|grant|group|having|holdlock|identity|identity_insert|identitycol|if|in|index|inner|insert|intersect|into|is|join|key|kill|left|like|lineno|load|national|nocheck|nonclustered|not|null|nullif|of|off|offsets|on|open|opendatasource|openquery|openrowset|openxml|option|or|order|outer|over|percent|plan|precision|primary|print|proc|procedure|public|raiserror|read|readtext|reconfigure|references|replication|restore|restrict|return|revoke|right|rollback|rowcount|rowguidcol|rule|save|schema|select|session_use|set|setuser|shutdown|some|statistics|system_user|table|textsize|then|to|top|tran|transaction|trigger|truncate|tsequal|union|unique|update|updatetext|use|user|values|varying|view|waitfor|when|where|while|with|writetext$", options);
            bool isMatch = regex.IsMatch(p);
            return isMatch;
        }
        private bool isWhen(string p)
        {
            RegexOptions options = RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase;
            Regex regex = new Regex(@"^when|then|else|end$", options);
            bool isMatch = regex.IsMatch(p);
            return isMatch;
        }
        private bool isCase(string p)
        {
            RegexOptions options = RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase;
            Regex regex = new Regex(@"^case$", options);
            bool isMatch = regex.IsMatch(p);
            return isMatch;
        }


        private string encodeString(string input)
        {
            string meh = input;
            meh = meh.Replace("'", "qqqapostropheqqq");
            meh = meh.Replace(".", "zzzperiodzzz");
            meh = meh.Replace("+", "xxxplusxxx");
            meh = meh.Replace("-", "yyysubtractyyy");
            meh = meh.Replace("(", "bbbopenbracketbb");
            meh = meh.Replace(")", "oooclosebracketooo");
            meh = meh.Replace("*", "fffasterixfff");
            meh = meh.Replace("/", "jjjdivisorjjj");
            meh = meh.Replace(",", "hhhcommahhh");
            meh = meh.Replace("=", "kkkequalskkk");
            meh = meh.Replace("[", "kkkhardopenkkk");
            meh = meh.Replace("]", "kkkhardclosekkk");
            meh = meh.Replace("inner join", "innerjoin");
            meh = meh.Replace("right join", "rightjoin");
            meh = meh.Replace("left join", "leftjoin");
            meh = meh.Replace("outer join", "outerjoin");
            meh = meh.Replace("cross join", "crossjoin");
            return meh;
        }
        private string unEncodeString(string input)
        {
            string meh = input;
            meh = meh.Replace("qqqapostropheqqq", "'");
            meh = meh.Replace("zzzperiodzzz", ".");
            meh = meh.Replace("xxxplusxxx", "+");
            meh = meh.Replace("yyysubtractyyy", "-");
            meh = meh.Replace("bbbopenbracketbb", "(");
            meh = meh.Replace("oooclosebracketooo", ")");
            meh = meh.Replace("fffasterixfff", "*");
            meh = meh.Replace("jjjdivisorjjj", "/");
            meh = meh.Replace("hhhcommahhh", ",");
            meh = meh.Replace("kkkequalskkk", "=");
            meh = meh.Replace("kkkhardopenkkk", "[");
            meh = meh.Replace("kkkhardclosekkk", "]");
            meh = meh.Replace("innerjoin", "inner join");
            meh = meh.Replace("rightjoin", "right join");
            meh = meh.Replace("leftjoin", "left join");
            meh = meh.Replace("outerjoin", "outer join");
            meh = meh.Replace("crossjoin", "cross join");
            return meh;
        }
        private void isNotEmpty(string input, out bool response)
        {
            if (input.Length > 0)
            {
                response = true;
            }
            else
            {
                response = false;
            }
        }
        private string[] DoSplit()
        {
            // Regex search and replace
            RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace;
            Regex regex = new Regex(@"\b", options);

            string[] myArray = regex.Split(Start);

            int length = myArray.Count();

            string[] myArray2 = new string[length];
            int x = 0;
            for (int i = 0; i < length; i++)
            {
                if (myArray[i].Trim().Length > 0)
                {
                    myArray2[x] = myArray[i].Trim();
                    x++;
                }
            }
            string[] myArray3 = new string[x];
            myArray3 = (String[])DGen.ResizeArray(myArray2, x);

            return myArray3;

        }


        private static string eliminateDuplicateNewLines(string input)
        {
            string output = "";
            output = Regex.Replace(input, @"\n\s*\n\s*|\n\n|\s*\n", "\n");
            return output;
        }

        void EliminateNewLines()
        {
            RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.Multiline;
            Regex regex = new Regex(Environment.NewLine, options);

            string replacement = @" ";
            WIP = regex.Replace(WIP, replacement);

        }
        void LinesAfterCommas()
        {
            RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.Multiline;
            Regex regex = new Regex(",", options);

            string replacement = @"," + Environment.NewLine + "\t\t";
            WIP = regex.Replace(WIP, replacement);

        }
        void FromClause()
        {
            RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.Multiline;
            Regex regex = new Regex(" FROM ", options);

            string replacement = Environment.NewLine + "FROM" + Environment.NewLine + "\t\t"; ;
            WIP = regex.Replace(WIP, replacement);

        }
        void LeftJoin()
        {
            RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.Multiline;
            Regex regex = new Regex(" LEFT JOIN ", options);
            string replacement = Environment.NewLine + "LEFT JOIN" + Environment.NewLine + "\t\t"; ;
            WIP = regex.Replace(WIP, replacement);
        }



        
    }
}

//TODO : Remove this.
/*
 * #region closethiscrap

        // Regex Match code for C#
        void KeywordMatch(string input)
        {
            input = input.ToLower();
            // Regex match
            RegexOptions options = RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace;
            Regex regex = new Regex(@"(\b(add|all|alter|and|any|as|asc|authorization|backup|begin|between|break|browse|bulk|by|cascade|case|check|checkpoint|close|clustered|coalesce|collate|column|commit|compute|constraint|contains|containstable|continue|convert|create|cross|current|current_date|current_time|current_timestamp|current_user|cursor|database|dbcc|deallocate|declare|default|delete|deny|desc|disk|distinct|distributed|double|drop|dummy|dump|else|end|errlvl|escape|except|exec|execute|exists|exit|fetch|file|fillfactor|for|foreign|freetext|freetexttable|from|full|function|goto|grant|group|having|holdlock|identity|identity_insert|identitycol|if|in|index|inner|insert|intersect|into|is|join|key|kill|left|like|lineno|load|national|nocheck|nonclustered|not|null|nullif|of|off|offsets|on|open|opendatasource|openquery|openrowset|openxml|option|or|order|outer|over|percent|plan|precision|primary|print|proc|procedure|public|raiserror|read|readtext|reconfigure|references|replication|restore|restrict|return|revoke|right|rollback|rowcount|rowguidcol|rule|save|schema|select|session_use|set|setuser|shutdown|some|statistics|system_user|table|textsize|then|to|top|tran|transaction|trigger|truncate|tsequal|union|unique|update|updatetext|use|user|values|varying|view|waitfor|when|where|while|with|writetext|inner join|outer join|right join|left join|cross join)\b)", options);
            WIP = input.ToLower();
            // Get matches
            MatchCollection matches = regex.Matches(input);
            for (int i = 0; i != matches.Count; ++i)
            {
      
                string something = matches[i].Value;
                string thingSearch = @"\b(" + something + @")\b";

                //System.Windows.Forms.MessageBox.Show(thingSearch, "Match");
                Regex regex2 = new Regex(thingSearch, options);
                string replacement2 = something.ToUpper();
                WIP = regex2.Replace(WIP, replacement2);

            }

        }
        // Regex Replace code for C#
        void ReplaceRegex(string input)
        {
            // Regex search and replace
            RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace;
            Regex regex = new Regex(@"\b", options);
            string replacement = @"7714332qqqzzzqqq7714332";
            string WIP = regex.Replace(input, replacement);

            //System.Windows.Forms.MessageBox.Show(result, "Replace");
        }

        // Regex Split code for C#

        #endregion
 * 
 *         //private void DealWithCase()
        //{
        //    string input = "";
        //    input = Start;
        //    KeywordMatch(input);
        //    ReplaceRegex(WIP);
        //    EliminateNewLines();
        //    LinesAfterCommas();
        //    FromClause();
        //    LeftJoin();
        //    EndResult = WIP;
        //}
 * 
 * */
